package com.funo.ehealth.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.funo.ehealth.bean.Service;
import com.funo.ehealth.bean.UserOrder;
import com.funo.ehealth.exception.SQLDOException;
import com.funo.ehealth.util.DBUtil;
import com.funo.ehealth.util.LogUtil;
import com.funo.ehealth.util.StringUtil;

/** 
 * 用户订购关系DAO
 * 
 * @version V1.1
 * @author zhangguangying
 * @date 2012-11-17
 */
public class UserOrderDao {
	
	/**
	 * UserOrderDao日志记录器
	 */
	private static final Log log = LogFactory.getLog(UserOrderDao.class);

	/**
	 * 记录错误信息到错误日志表
	 * @param logType 日志类型
	 * @param desp 日志描述
	 * @return int
	 */
	public int insertErrLog(int logType,String desp){
		String sql = "insert into errLog (ID,Occur_Time,Log_Type,Desp) values (SEQ_ERRLOG.nextval,sysdate,?,?)";
		Connection conn = null;
		PreparedStatement pstmt = null;
		int res = 0;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = DBUtil.getPreparedStatement(conn, sql);
			
			LogUtil.debug(log,"insertErrLog,参数：{sql:{},logType:{},desp:{}}", sql,logType,desp);
			pstmt.setInt(1, logType);
			pstmt.setString(2, desp);
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
//			LogUtil.error(log, e, "insertErrLog异常,参数：{sql:{},logType:{},desp:{}}", sql,logType,desp);
			throw new SQLDOException(StringUtil.handleString("insertErrLog异常,参数：{sql:{},logType:{},desp:{}}", sql,logType,desp), e);
		} finally {
			DBUtil.closeAll(conn, pstmt, null);
		}
		return res;
	}
	
	/**
	 * 删除用户订购关系临时表userorder_wait的单条记录
	 * @param userTEL 用户手机号码
	 * @param serviceCode 业务代码
	 * @return int
	 */
	public int deleteUserOrderWait(String userTEL,String serviceCode){
		String sql = "delete from userorder_wait where User_TEL=? and Servicelist_ID in (select id from servicelist where Service_Code=?)";
		Connection conn = null;
		PreparedStatement pstmt = null;
		int res = 0;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = DBUtil.getPreparedStatement(conn, sql);
			
			LogUtil.debug(log, "deleteUserOrderWait,参数：{sql:{},userTEL:{},serviceCode:{}}", sql,userTEL,serviceCode);
			pstmt.setString(1, userTEL);
			pstmt.setString(2, serviceCode);
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
//			LogUtil.error(log, e, "deleteUserOrderWait异常,参数：{sql:{},userTEL:{},serviceCode:{}}", sql,userTEL,serviceCode);
			throw new SQLDOException(StringUtil.handleString("deleteUserOrderWait异常,参数：{sql:{},userTEL:{},serviceCode:{}}", sql,userTEL,serviceCode), e);
		} finally {
			DBUtil.closeAll(conn, pstmt, null);
		}
		
		return res;
	}
	
	/**
	 * 取得业务代码对应的CP订购关系同步URL列表
	 * @param serviceCode 业务代码
	 * @return String
	 */
	public String queryOrderURL(String serviceCode){
//		String sql = "select c.CP_ServiceNotifi from cpList c left join servicelist s on c.ID=s.CPList_ID where s.Service_Code=?";
		String sql = "select cs.id,c.cp_servicenotifi from cplist c,servicelist s,cplist_servicelist cs where s.id=cs.servicelist_id and c.id=cs.cplist_id and s.service_code=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String cpurl = null;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = DBUtil.getPreparedStatement(conn, sql);
			
			LogUtil.debug(log,"queryOrderURL,参数：{sql:{},serviceCode:{}}", sql,serviceCode);
			pstmt.setString(1, serviceCode);
			res = pstmt.executeQuery();
			if(res != null && res.next()){
				cpurl = res.getString("CP_ServiceNotifi");
			}
		} catch (SQLException e) {
//			LogUtil.error(log,e,"queryOrderURL异常,参数：{sql:{},serviceCode:{}}", sql,serviceCode);
			throw new SQLDOException(StringUtil.handleString("queryOrderURL异常,参数：{sql:{},serviceCode:{}}", sql,serviceCode), e);
		} finally {
			DBUtil.closeAll(conn, pstmt, res);
		}
		
		return cpurl;
	}
	
	/**
	 * 修改用户订购关系表cancelflag为0(已取消)
	 * @param cpSyncFlag 是否已同步到CP
	 * @param id 订购关系表Id
	 * @return int
	 */
	public int updateCpSyncFlag(int cpSyncFlag,long id){
		String sql = "update userorder set CPSYNCFLAG=? where id=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		int res = 0;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = DBUtil.getPreparedStatement(conn, sql);
			
			LogUtil.debug(log,"updateCpSyncFlag,参数：{sql:{},cpSyncFlag:{},id:{}}", sql,cpSyncFlag,id);
			pstmt.setInt(1, cpSyncFlag);
			pstmt.setLong(2, id);
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
//			LogUtil.error(log, e, "updateCpSyncFlag异常,参数：{sql:{},cpSyncFlag:{},id:{}}", sql,cpSyncFlag,id);
			throw new SQLDOException(StringUtil.handleString("updateCpSyncFlag异常,参数：{sql:{},cpSyncFlag:{},id:{}}", sql,cpSyncFlag,id), e);
		} finally {
			DBUtil.closeAll(conn, pstmt, null);
		}
		
		return res;
	}
	
	/**
	 * 取得未同步到CP端的订购关系列表
	 * @return List<UserOrder>
	 */
	public void queryUserOrderList(List userOrderList){
//		String sql = "select f.value as subMsisdn,u.id as id,u.user_tel as usertel,s.service_code as servicecode,u.cancelflag as cancelflag,c.cp_servicenotifi as cpUserOrderUrl from userorder u,servicelist s,cplist c,cplist_servicelist cs,t_cust_ord_fea f where u.servicelist_id=s.id and u.CPSYNCFLAG=1 and (f.user_order_id=u.id and f.type='0002' and f.status='10C') and (cancelflag=0 or cancelflag=1) and s.id=cs.servicelist_id and c.id=cs.cplist_id and c.cp_servicenotifi is not null";
		String sql = "select f.value as subMsisdn,u.id as id,u.user_tel as usertel,s.service_code as servicecode,u.cancelflag as cancelflag,c.cp_servicenotifi as cpUserOrderUrl from userorder u,servicelist s,cplist c,cplist_servicelist cs,t_cust_ord_fea f where u.servicelist_id=s.id and ((u.cancelflag=1 and u.CPSYNCFLAG=1) or (u.cancelflag=0 and u.CPSYNCFLAG=0)) and (f.user_order_id=u.id and f.type='0002' and f.status='10C') and s.id=cs.servicelist_id and c.id=cs.cplist_id and c.cp_servicenotifi is not null";
		Connection conn = null;
		PreparedStatement pstmt = null;
		UserOrder userOrder = null;
		ResultSet res = null;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = DBUtil.getPreparedStatement(conn, sql);
		
			LogUtil.debug(log,"queryUserOrderList,参数：{sql:{}}", sql);
			res = pstmt.executeQuery();
			while (res.next()) {
				userOrder = new UserOrder();
				userOrder.setId(res.getInt("id"));
				userOrder.setDestUser_ID(res.getString("usertel"));
				userOrder.setSpServid(res.getString("servicecode"));
				userOrder.setCancelFlag(res.getInt("cancelflag"));
				if("1".equals(String.valueOf(res.getInt("cancelflag")))){
					userOrder.setActionID("06");
				}else if("0".equals(String.valueOf(res.getInt("cancelflag")))){
					userOrder.setActionID("07");
				}
				userOrder.setCpUserOrderUrl(res.getString("cpUserOrderUrl"));
				userOrder.setSubMsisdn(res.getString("subMsisdn"));
				userOrderList.add(userOrder);
			}
		} catch (Exception e) {
			LogUtil.error(log, e, "queryUserOrderList异常,参数：{sql:{}}", sql);
//			throw new SQLDOException(StringUtil.handleString("queryUserOrderList异常,参数：{sql:{}}", sql), e);
		} finally {
			DBUtil.closeAll(conn, pstmt,res);
		}
	}
	
	/**
	 * 取得业务代码对应的业务对象
	 * @param serviceCode 业务代码
	 * @return Service
	 */
	public Service queryService(String serviceCode){
		String sql = "select * from servicelist where service_code=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		Service service = new Service();
		ResultSet res = null;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = DBUtil.getPreparedStatement(conn, sql);
			
			LogUtil.debug(log,"queryService,参数：{sql:{},serviceCode:{}}", sql,serviceCode);
			pstmt.setString(1, serviceCode);
			res = pstmt.executeQuery();
			if(res.next()){
				service.setId(res.getInt("ID"));
				service.setCommType(String.valueOf(res.getInt("COMM_TYPE")));
//				service.setAllTheSame(String.valueOf(res.getInt("ALL_THE_SAME")));
				service.setProductCode(res.getString("PRODUCT_CODE"));
			}
		} catch (SQLException e) {
//			LogUtil.error(log, e, "queryService异常,参数：{sql:{},serviceCode:{}}", sql,serviceCode);
			throw new SQLDOException(StringUtil.handleString("queryService异常,参数：{sql:{},serviceCode:{}}", sql,serviceCode), e);
		} finally {
			DBUtil.closeAll(conn, pstmt,res);
		}
		return service;
	}
	
	/**
	 * 取得未同步到VGOP端的订购/退订关系列表
	 * @return List<UserOrder>
	 */
	public void queryVgopOrderList(int cancelflag,List vgopOrderList){
		String sql = "select t.id orderWaitId,s.id,s.service_code,s.cost_type,t.user_tel,s.subservtype from userorder_wait t,servicelist s where t.cancelflag=? and (t.istovgop is null or t.istovgop!=0) and t.servicelist_id=s.id";
		Connection conn = null;
		PreparedStatement pstmt = null;
		Service service = null;
		ResultSet res = null;
		
		try{
			conn = DBUtil.getConnection();
			pstmt = DBUtil.getPreparedStatement(conn, sql);
		
			LogUtil.debug(log,"queryVgopOrderList,参数：{sql:{},cancelflag:{}}", sql,cancelflag);
			pstmt.setInt(1, cancelflag);
			res = pstmt.executeQuery();
			while (res.next()) {
				service = new Service();
				service.setId(res.getInt("id"));
				service.setOrderWaitId(res.getInt("orderWaitId"));
				service.setServiceCode(res.getString("service_code"));
				service.setCostType(res.getInt("cost_type"));
				service.setUserTel(res.getString("user_tel"));
				service.setSubservtype(res.getString("subservtype"));
				vgopOrderList.add(service);
			}
		} catch (Exception e) {
			LogUtil.error(log, e, "queryVgopOrderList异常,参数：{sql:{},cancelflag:{}}", sql,cancelflag);
//			throw new SQLDOException(StringUtil.handleString("queryVgopOrderList异常,参数：{sql:{},cancelflag:{}}", sql,cancelflag), e);
		} finally {
			DBUtil.closeAll(conn, pstmt,res);
		}
	}
	
	/**
	 * 取得SEQ_USERORDERID.nextval的值msgtransactionid
	 * @return <b>String</b>
	 */
	public static String querySeqUserOrderId(){
		String sql = "select lpad(SEQ_USERORDERID.nextval,12,'0') as msgtransactionid from dual";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String msgtransactionid = null;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			log.debug("querySeqUserOrderId，sql："+sql);
			res = pstmt.executeQuery();
			if(res != null && res.next()){
				msgtransactionid = res.getString("msgtransactionid");
			}
		} catch (SQLException e) {
//			log.error("querySeqUserOrderId异常，sql："+sql,e);
			throw new SQLDOException("querySeqUserOrderId异常,sql："+sql, e);
		} finally {
			DBUtil.closeAll(conn, pstmt,res);
		}
		
		return msgtransactionid;
	}
	
	/**
	 * 取得单条订购的订购生效时间
	 * @param servicelistID 业务管理表ID
	 * @param srcterminalId 用户手机号
	 * @return String
	 */
	public static String queryEffTime(String servicelistID,String srcterminalId){
		String sql = 
			"select effTime " +
			"from userorder " +
			"where servicelist_id=? " +
			"and user_tel=? " +
			"and cancelflag=1";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String effTime = null;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setLong(1, Long.parseLong(servicelistID));
			pstmt.setString(2, srcterminalId);
			log.debug(StringUtil.handleString("queryEffTime,sql:{},servicelistID:{},srcterminalId:{}", sql,servicelistID,srcterminalId));
			
			res = pstmt.executeQuery();
			if(res.next()){
				effTime = res.getString("effTime");
			}
		} catch (SQLException e) {
//			log.error(StringUtil.handleString("queryEffTime异常,sql:{},servicelistID:{},srcterminalId:{}", sql,servicelistID,srcterminalId),e);
			throw new SQLDOException(StringUtil.handleString("queryEffTime异常,sql:{},servicelistID:{},srcterminalId:{}", sql,servicelistID,srcterminalId), e);
		} finally {
			DBUtil.closeAll(conn, pstmt,res);
		}
		
		return effTime;
	}
	
	/**
	 * 将订购信息插入到订购关系表
	 * @param srcterminalId 源号码
	 * @param servicelistID 业务管理表Id
	 * @param msgtransationId_order 订购的事务Id
	 * @param effTime 生效时间
	 * @return <b>int</b>
	 */
	public static int insertUserOrder(String srcterminalId,String servicelistID,String msgtransationId_order,String effTime){
		final String sql = "insert into userorder " +
				"(id," +
				"user_tel," +
				"servicelist_id," +
				"order_time," +
				"cppxy_list_id," +
				"cancelflag," +
				"cpsyncflag," +
				"transactionid," +
				"msgtransactionid_order," +
				"msgtransactionid_cancel," +
				"cfmresult," +
				"efftime," +
				"expiretime) " +
			"values (" +
				"SEQ_USERORDER.nextval," +
				"?," +
				"?," +
				"sysdate," +
				"0," +
				"2," +
				"1," +
				"''," +
				"?," +
				"''," +
				"''," +
				"?," +
				"'30000101000000')";
		Connection conn = null;
		PreparedStatement pstmt = null;
		int res = 0;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, srcterminalId);
			pstmt.setLong(2, Long.parseLong(servicelistID));
			pstmt.setString(3, msgtransationId_order);
			pstmt.setString(4, effTime);
			
			log.debug(StringUtil.handleString("insertUserOrder,sql：{},srcterminalId:{},servicelistID:{},msgtransationId_order:{},effTime:{}", sql,srcterminalId,servicelistID,msgtransationId_order,effTime));
			
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
//			log.error(StringUtil.handleString("insertUserOrder异常,sql：{},srcterminalId:{},servicelistID:{},msgtransationId_order:{},effTime:{}", 
//					sql,srcterminalId,servicelistID,msgtransationId_order,effTime),e);
			throw new SQLDOException(StringUtil.handleString("insertUserOrder异常,sql：{},srcterminalId:{},servicelistID:{},msgtransationId_order:{},effTime:{}", 
					sql,srcterminalId,servicelistID,msgtransationId_order,effTime), e);
		} finally {
			DBUtil.closeAll(conn, pstmt, null);
		}
		return res;
	}
	
	/**
	 * 删除订购关系临时表userorder_wait记录
	 * @param userOrderWaitId
	 * @return
	 * @throws SMException 
	 */
	public static int deleteUserorderWait(long userOrderWaitId){
		final String sql = "delete from userorder_wait where id=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		int res = 0;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setLong(1, userOrderWaitId);
			log.debug(StringUtil.handleString("deleteUserorderWait,参数：{sql:{},userOrderWaitId:{}}",sql,userOrderWaitId));
			
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
//			log.error("deleteUserorderWait异常",e);
			throw new SQLDOException(StringUtil.handleString("deleteUserorderWait异常,参数：{sql:{},userOrderWaitId:{}}",sql,userOrderWaitId), e);
		} finally {
			DBUtil.closeAll(conn, pstmt,null);
		}
		
		return res;
	}
	
	/**
	 * 修改用户订购关系表
	 * @param msgtransationId_cancel 退订的事务Id
	 * @param expireTime 失效时间
	 * @param servicelistID 业务管理表Id
	 * @param srcterminalId 源号码
	 * @return <b>int</b>
	 */
	public static int updateUserOrder(String msgtransationId_cancel,String expireTime,String servicelistID,String srcterminalId){
		final String sql = 
				"update userorder " +
				"set cancelflag=4,msgtransactionid_cancel=?,expiretime=? " +
				"where servicelist_id=? " +
				"and user_tel=? " +
				"and cancelflag=1";
		Connection conn = null;
		PreparedStatement pstmt = null;
		int res = 0;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, msgtransationId_cancel);
			pstmt.setString(2, expireTime);
			pstmt.setLong(3, Long.parseLong(servicelistID));
			pstmt.setString(4, srcterminalId);
			
			log.debug(StringUtil.handleString("updateUserOrder,sql:{},msgtransationId_cancel:{},expireTime:{},servicelistID:{},srcterminalId:{}", sql,msgtransationId_cancel,expireTime,servicelistID,srcterminalId));
			
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
//			log.error(StringUtil.handleString("updateUserOrder异常,sql:{},msgtransationId_cancel:{},expireTime:{},servicelistID:{},srcterminalId:{}", 
//					sql,msgtransationId_cancel,expireTime,servicelistID,srcterminalId),e);
			throw new SQLDOException(StringUtil.handleString("updateUserOrder异常,sql:{},msgtransationId_cancel:{},expireTime:{},servicelistID:{},srcterminalId:{}", 
					sql,msgtransationId_cancel,expireTime,servicelistID,srcterminalId), e);
		} finally {
			DBUtil.closeAll(conn, pstmt, null);
		}
		return res;
	}
	
	/**
	 * 读取订购关系表中用户手机对应的已订购当前业务的信息条数
	 * @param userTel
	 * @param servicelistId
	 * @param cancelflag
	 * @return
	 * @throws SMException 
	 */
	public static int queryUserOrderCountByUser(String userTel, long servicelistId, int cancelflag){
		final String sql = "select count(*) count from userorder t where t.user_tel=? and t.servicelist_id=? and t.cancelflag=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet res = null;
		int count = 0;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			log.debug(StringUtil.handleString("queryUserOrderCountByUser,参数：{sql:{},userTel:{},servicelistId:{},cancelflag:{}}",sql,userTel,servicelistId,cancelflag));
			pstmt.setString(1, userTel);
			pstmt.setLong(2, servicelistId);
			pstmt.setInt(3, cancelflag);
			
			res = pstmt.executeQuery();
			if(res != null && res.next()){
				count = res.getInt("count");
			}
		} catch (SQLException e) {
//			log.error("queryUserOrderCountByUser异常",e);
			throw new SQLDOException(StringUtil.handleString("queryUserOrderCountByUser异常,参数：{sql:{},userTel:{},servicelistId:{},cancelflag:{}}",sql,userTel,servicelistId,cancelflag), e);
		} finally {
			DBUtil.closeAll(conn, pstmt,res);
		}
		
		return count;
	}
	
	/**
	 * 修改userorder_wait表的ISTOVGOP标识
	 * @param istovgop
	 * @param id
	 * @return 
	 */
	public static int updateUserorderWaitByIstovgop(int istovgop,int id){
		final String sql = "update userorder_wait set istovgop=? where id=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		int res = 0;
		
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, istovgop);
			pstmt.setLong(2, id);
			log.debug(StringUtil.handleString("updateUserorderWaitByIstovgop,参数：{sql:{},istovgop:{},id:{}}",sql,istovgop,id));
			
			res = pstmt.executeUpdate();
		} catch (SQLException e) {
			log.error("updateUserorderWaitByIstovgop异常",e);
//			throw new SMException("修改userorder_wait表的ISTOVGOP标识失败", e);
		} finally {
			DBUtil.closeAll(conn, pstmt,null);
		}
		
		return res;
	}
	
}
